import os
import re
import sqlite3
import subprocess
import sys
from typing import Tuple

from loguru import logger

from mdtool.config_reader import Config


class DbMigrator:
    DB_SET = None
    DB_SET_STORE = ""
    ROOT_DIR = ""
    DB_VER_STORE = ""
    DB_SCHEMA_DIR = "db_schema"
    DB_VERSION = 0

    def __init__(self, config: Config) -> None:
        self.ROOT_DIR = config.get("articledir")
        self.DB_SET_STORE = str(config.get("db.storefile"))
        self.DB_VER_STORE = str(config.get("db.verfile"))

    def _get_all_sql_files(self, db_schema_dir):
        """Finds all SQL migration files and extracts their versions."""
        sql_files = []
        if os.path.exists(db_schema_dir) and os.path.isdir(db_schema_dir):
            for filename in os.listdir(db_schema_dir):
                match = re.match(r"^(\d+)_.*\.sql$", filename)
                if match:
                    version = int(match.group(1))
                    sql_files.append((version, os.path.join(db_schema_dir, filename)))
        sql_files.sort()
        return sql_files

    def _check_all_py_files(self, sql_files):
        """Checks if corresponding Python scripts exist for each SQL migration."""
        result = True
        count = 0
        for _, sql_file in sql_files:
            py_file = sql_file.replace(".sql", ".py")
            if not os.path.exists(py_file):
                result = False
                count += 1
                logger.warning(
                    f"Warning: No corresponding Python script found for {os.path.basename(sql_file)}"
                )
        if result:
            logger.debug("All SQL migration files have corresponding Python scripts.")
        else:
            logger.warning(
                f"Total {count} SQL migration files are missing corresponding Python scripts."
            )
        return result

    def _execute_sql_migration(self, sql_file):
        """Executes a single SQL migration file."""
        logger.info(f"Applying migration: {os.path.basename(sql_file)}")
        command = f'sqlite3 "{self.DB_SET_STORE}" < "{sql_file}"'
        logger.debug(f"command = {command}")
        try:
            with open(sql_file, "r") as f:
                sql_content = f.read()
            result = subprocess.run(
                ["sqlite3", self.DB_SET_STORE],  # 只传递sqlite3程序和数据库文件
                input=sql_content,  # 通过input参数传递SQL内容
                capture_output=True,
                text=True,
            )
            if result.returncode != 0:
                logger.error(f"Failed to execute {(sql_file)}: {result.stderr}")
                sys.exit(1)
            logger.info(f"Successfully applied migration: {os.path.basename(sql_file)}")
        except Exception as e:
            logger.error(f"Failed to execute {(sql_file)}: {e}")
            # Depending on requirements, you might want to exit or handle the error differently
            sys.exit(1)

    def _execute_python_migration(self, py_file):
        """Executes a single Python migration script."""
        if os.path.exists(py_file):
            logger.info(f"  Running post-upgrade script: {os.path.basename(py_file)}")
            try:
                # This is a simplified way to run a Python file.
                # A more robust approach might involve importing and calling a specific function.
                result = subprocess.run(
                    ["python", py_file, self.ROOT_DIR, self.DB_SET_STORE],
                    capture_output=True,
                    text=True,
                )
                if result.returncode != 0:
                    logger.error(f"  Failed to execute {(py_file)}: {result.stderr}")
                    sys.exit(1)
                logger.info(f"  Successfully executed {os.path.basename(py_file)}")
            except Exception as e:
                result = str(e)
                logger.warning(f"  Failed to execute {(py_file)}: {result}")
                sys.exit(1)

    def _get_current_version(self, db_version_file) -> int:
        """Reads the current database version from the version file."""
        current_version = 0
        # 尝试从数据库中读取当前版本
        db_path = self.DB_SET_STORE
        if os.path.exists(db_path):
            conn = None
            try:
                conn = sqlite3.connect(db_path)
                cursor = conn.cursor()
                cursor.execute("SELECT db_ver FROM db_version DESC LIMIT 1")
                row = cursor.fetchone()
                if row:
                    current_version = row[0]
                    return current_version
            except sqlite3.OperationalError as e:
                logger.warning(
                    f"Database {db_path} is not found or other DB error: {e}."
                )
                # If table doesn't exist, proceed to read from file
            except Exception:
                logger.error(
                    f"An unexpected error occurred while reading from database: {db_path}"
                )
                sys.exit(1)  # Or handle more gracefully
            finally:
                if conn:
                    conn.close()

        # 如果版本文件也不存在，则判断为版本 0
        if not os.path.exists(db_version_file):
            logger.debug(f"无法读取当前版本文件，（{db_version_file}），默认是版本 0.")
            current_version = 0
            return current_version
        # 如果版本文件存在，则读取版本,应该为 0 或者 1
        try:
            with open(db_version_file, "r") as f:
                content = f.read().strip()
            if content.isdigit():
                current_version = int(content)
                logger.info(f"Current database version: {current_version}")
                return current_version
            else:
                logger.error(
                    f"当前版本文件 {db_version_file} 的内容不是正确的版本号，而是: {content}"
                )
                sys.exit(1)
        except Exception as e:
            logger.error(f"catch Exception when _get_current_version: \n {e}")
            sys.exit(1)

    def _get_upgrade_script_pairs(self, db_schema_dir: str):
        """Checks if corresponding Python scripts exist for each SQL migration."""
        sql_files = self._get_all_sql_files(db_schema_dir)
        if len(sql_files) == 0:
            logger.error(f"Database schema directory is empty: {db_schema_dir}")
            sys.exit(1)
        if not self._check_all_py_files(sql_files):
            logger.error(
                "Some SQL migration files are missing corresponding Python scripts."
            )
            sys.exit(1)
        return sql_files

    def migrate(self) -> Tuple[int, int, int]:
        logger.info("开始检查数据库版本，如有需要，程序会自动升级数据库。")
        db_schema_dir = os.path.join(os.path.dirname(__file__), self.DB_SCHEMA_DIR)
        logger.debug(f"Database schema directory: {db_schema_dir}")
        db_version_file = os.path.join(self.ROOT_DIR, self.DB_VER_STORE)

        start_ver = self._get_current_version(db_version_file)
        end_ver = start_ver
        # 得到 max_version 和 start_ver 中最大的值
        sql_files = self._get_upgrade_script_pairs(db_schema_dir)
        max_ver = sql_files[-1][0] if sql_files else 0
        logger.debug(
            f"start_ver: {start_ver}, max_ver: {max_ver}, db_version_file: {db_version_file}"
        )
        if start_ver > max_ver:
            logger.error(
                f"Database version {start_ver} is higher than the highest available migration script version {max_ver}."
            )
            sys.exit(1)

        if start_ver == max_ver:
            logger.info("数据库已经是最新版本，不必升级。")
            return start_ver, max_ver, max_ver
        if start_ver < max_ver:
            logger.info(f"数据库最新版本是: {max_ver}")
            logger.info("数据库自动升级中 ... ...")

            for version, sql_file in sql_files:
                if (
                    version <= start_ver
                ):  # Skip if version is less than or equal to start_ver
                    continue
                try:  # Execute the SQL script
                    self._execute_sql_migration(sql_file)
                    # Check for and run corresponding Python script

                    py_file = sql_file.replace(".sql", ".py")
                    self._execute_python_migration(py_file)
                    logger.info(f"\t 已成功升级到版本： {version}")
                    # Update version file after successful execution
                    with open(db_version_file, "w") as f:
                        f.write(str(version))
                    end_ver = version  # Update current version
                except Exception as e:
                    logger.error(
                        f"Error during database upgrade with {os.path.basename(sql_file)}: {e}"
                    )
                    break  # Stop on first error for now
        logger.info("数据库升级完成。")
        logger.info(
            f"初始版本: {start_ver}\n当前版本 : {end_ver}。\n最高版本： {max_ver}"
        )
        return start_ver, end_ver, max_ver
